﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.WindowsAzure.ServiceRuntime;

using Domain;

namespace Repositories
{
	public class TopicRepository
	{
		string connStr;

		public SqlConnection Connection { get; set; }

		public SqlCommand Command { get; set; }

		public SqlConnection CreateConnection()
		{
			connStr = RoleEnvironment.GetConfigurationSettingValue("ConnectionString");

			Connection = new SqlConnection(connStr);

			return Connection;
		}

		public SqlCommand CreateCommand()
		{
			Command = new SqlCommand();

			return Command;
		}

		public SqlTransaction BeginTransaction()
		{
			if (Command == null)
			{
				CreateCommand();

				if (Connection == null)
				{
					CreateConnection();
				}

				Command.Connection = Connection;
			}

			Command.Connection.Open();

			return Command.Connection.BeginTransaction();
		}

		public TopicRepository()
		{
			CreateConnection();
		}

		public IList<Topic> getTopics(Int64 forumID)
		{
			SqlConnection conn = new SqlConnection(connStr);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "GetTopics";
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter parSubforumID = new SqlParameter("@subforumid", forumID);
			cmd.Parameters.Add(parSubforumID);

			conn.Open();

			SqlDataReader dr = cmd.ExecuteReader();

			List<Topic> topics = new List<Topic>();

			while (dr.Read())
			{
				Topic t = new Topic();

				t.ID = Int64.Parse(dr["ID"].ToString());

				t.LastPosterName = dr["LastPosterName"].ToString();
				t.LastPostOn = dr["LastPostOn"].ToString();
				t.StartedOn = dr["StartedOn"].ToString();
				t.Title = dr["Title"].ToString();
				t.UrlTitle = dr["UrlTitle"].ToString();
				t.TopicStarterName = dr["TopicStarterName"].ToString();
				t.TopicStarterID = dr["TopicStarterID"].ToString();
				t.SubforumName = dr["SubforumName"].ToString();
				t.LastPosterID = dr["LastPosterID"].ToString();

				if (dr["Posts"].ToString() != "")
				{
					t.Posts = Int64.Parse(dr["Posts"].ToString());
				}

				if (dr["Views"].ToString() != "")
				{
					t.Views = Int64.Parse(dr["Views"].ToString());
				}

				topics.Add(t);
			}

			conn.Close();

			return topics;
		}

		public System.Collections.Generic.IList<Topic> getActiveTopicsNew(string username)
		{
			SqlConnection conn = new SqlConnection(connStr);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "GetActiveTopics";
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter parUsername = new SqlParameter("@username", username);
			cmd.Parameters.Add(parUsername);

			conn.Open();
			SqlDataReader dr = cmd.ExecuteReader();

			List<Topic> topics = new List<Topic>();

			while (dr.Read())
			{
				Topic t = new Topic();
				t.ID = Int64.Parse(dr["ID"].ToString());
				t.LastPosterName = dr["LastPosterName"].ToString();
				t.LastPostOn = dr["LastPostOn"].ToString();
				t.StartedOn = dr["StartedOn"].ToString();
				t.Title = dr["Title"].ToString();
				t.UrlTitle = dr["UrlTitle"].ToString();
				t.TopicStarterName = dr["TopicStarterName"].ToString();
				t.TopicStarterID = dr["TopicStarterID"].ToString();
				t.LastPosterID = dr["LastPosterID"].ToString();

				if (dr["Posts"].ToString() != "")
				{
					t.Posts = Int64.Parse(dr["Posts"].ToString());
				}

				if (dr["SubforumID"].ToString() != "")
				{
					t.SubforumID = Int64.Parse(dr["SubforumID"].ToString());
				}				

				if (dr["Views"].ToString() != "")
				{
					t.Views = Int64.Parse(dr["Views"].ToString());
				}

				topics.Add(t);
			}

			conn.Close();

			return topics;
		}

		public Int64 createTopic(string title, string urlTitle, string content, Int16 subforumID, string topicStarterName, string topicStarterID, ref SqlConnection connection, ref SqlTransaction transaction)
		{
			Connection.Open();
			transaction = Connection.BeginTransaction();

			SqlCommand cmd = new SqlCommand("CreateTopic", Connection);
			cmd.Transaction = transaction;

			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("title", title));
			cmd.Parameters.Add(new SqlParameter("urlTitle", urlTitle));
			cmd.Parameters.Add(new SqlParameter("content", content));
			cmd.Parameters.Add(new SqlParameter("subforumID", subforumID));
			cmd.Parameters.Add(new SqlParameter("TopicStarterName", topicStarterName));
			cmd.Parameters.Add(new SqlParameter("TopicStarterID", topicStarterID));

			//if (Connection.State != ConnectionState.Open)
			//{
			//   Connection.Open();
			//   transaction = Connection.BeginTransaction();
			//}

			Int64 topicID = Int64.Parse(cmd.ExecuteScalar().ToString());

			connection = Connection;

			return topicID;
			//cmd.ExecuteNonQuery();
		}

		public string getTopicTitle(Int64 topicID)
		{
			SqlDataAdapter da = new SqlDataAdapter(
					"select Title from topics where id = " + topicID, Connection);

			DataSet dst = new DataSet();

			Connection.Open();
			da.Fill(dst);
			Connection.Close();

			return dst.Tables[0].Rows[0]["Title"].ToString();
		}
	}
}
